package dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import jdbc.jdbc;
import org.apache.commons.lang.StringUtils;
import pojo.CurriCulum;
import pojo.gai;
import tool.ResultSetToJson;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

public class CurriCulumDaoImp implements CurriCulumDao {
    /**
     * 显示当前用户的课表
     * 管理员可查询所有用户排课表
     *
     * @param teacherId
     * @return 返回老师排课结果
     */
    @Override
    public JSONArray UserTable(int teacherId, String sj) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String sql = "SELECT a.id,b.teacherName,a.classID,a.courseName,a.classHours,a.mark FROM schooltreasure.curriculum as a left join schooltreasure.teacher as b on a.teacherId=b.id left join  schooltreasure.time as c on a.classHours=c.id   left join schooltreasure.classtable as d on a.classID=d.classId where a.teacherId='" + teacherId + "' and dmy='" + sj + "' and a.invalid=0;";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        JSONArray jsonArray = new JSONArray();
        while (resultSet.next()) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("id", resultSet.getInt("id"));
            jsonObject.put("teacherName", resultSet.getString("teacherName"));
            jsonObject.put("classID", resultSet.getString("classID"));
            jsonObject.put("classHours", resultSet.getString("classHours"));
            jsonObject.put("courseName", resultSet.getString("courseName"));
            jsonObject.put("mark", resultSet.getInt("mark"));
            jsonArray.add(jsonObject);
        }

        jdbc.guanbi(null, preparedStatement, connection);
        return jsonArray;
    }

    /**
     * 用户添加课程安排
     *
     * @param
     * @return 返回1或0
     */
    @Override
    public int InsertKe(JSONArray jsonArrayint, int id) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String sql = "insert into schooltreasure.curriculum(teacherId,dmy,classHours,classID,courseName)values ";
        String dou = ",";
        for (int i = 0; i < jsonArrayint.size(); i++) {
            if (i == jsonArrayint.size() - 1) {
                dou = ";";
            }
            sql = sql + "('" + id + "','" + jsonArrayint.parseObject(jsonArrayint.get(i).toString()).get("sj") + "','" + (StringUtils.strip(jsonArrayint.parseObject(jsonArrayint.get(i).toString()).getString("keshi"), "[]")).replace("\"", "") + "','" + jsonArrayint.parseObject(jsonArrayint.get(i).toString()).get("ban") + "','" + jsonArrayint.parseObject(jsonArrayint.get(i).toString()).get("kname") + "')";
            sql = sql + dou;
        }


        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();

        jdbc.guanbi(null, preparedStatement, connection);
        return resultSet;
    }

    /**
     * 修改用户自己的课程安排
     *
     * @param curriCulum
     * @return 返回0或1
     */
    @Override
    public int UpdateUes(CurriCulum curriCulum) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String sql = "Update schooltreasure.curriculum set dmy='" + curriCulum.getDmy() + "',classHours='" + curriCulum.getClassHours() + "',classID='" + curriCulum.getClassID() + "',courseName='" + curriCulum.getCourseName() + "',mark='" + curriCulum.getMark() + "' where id='" + curriCulum.getId() + "'";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();

        jdbc.guanbi(null, preparedStatement, connection);
        return resultSet;
    }

    /**
     * 删除用户自己的课程安排
     *
     * @param curriCulum
     * @return 返回0或1
     */
    @Override
    public int DelUesr_Up(CurriCulum curriCulum) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String sql = " Update schooltreasure.curriculum set invalid='" + curriCulum.getInvalid() + "' where id='" + curriCulum.getId() + "';";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();

        jdbc.guanbi(null, preparedStatement, connection);
        return resultSet;
    }

    /**
     * 打卡已上课程
     *
     * @param curriCulum
     * @return 返回1或0
     */
    @Override
    public int ClockIn(CurriCulum curriCulum, String xx) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String sql = "Update schooltreasure.curriculum set mark='" + curriCulum.getMark() + "' where teacherId='" + curriCulum.getTeacherId() + "' and dmy='" + curriCulum.getDmy() + "' and classHours='" + xx + "';";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();

        jdbc.guanbi(null, preparedStatement, connection);
        return resultSet;
    }

    /**
     * 管理员给用户设置权限
     *
     * @param teacherId
     * @return 返回1或0
     */
    @Override
    public int SetUser(int teacherId, int level) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String sql = "update schooltreasure.teacher set level='" + level + "' where id='" + teacherId + "';";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();

        jdbc.guanbi(null, preparedStatement, connection);
        return resultSet;
    }

    /**
     * 管理员查看用户总课时
     *
     * @param teacherId
     * @return 返回总课时
     */
    @Override
    public int CheckUser(int teacherId) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String sql = "SELECT count(*) as kehour FROM schooltreasure.curriculum where teacherId='" + teacherId + "';";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        int jie = 0;
        while (resultSet.next()) {
            jie = resultSet.getInt("kehour");
        }

        jdbc.guanbi(null, preparedStatement, connection);
        return jie;
    }

    /**
     * 用户新增课程时查重
     *
     * @param
     * @param
     * @return
     */
    @Override
    public ArrayList<String> ChecChong(int id, JSONArray jsonArray) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String dou = ",";
        String or = "or";
        String sql = "select a.id,substring_index(substring_index(a.classHours,',',b.help_topic_id+1),',',-1)as b from schooltreasure.curriculum a join mysql.help_topic b on b.help_topic_id < (length(a.classHours) - length(replace(a.classHours,',',''))+1) where";
        for (int i = 0; i < jsonArray.size(); i++) {
            if (i == jsonArray.size() - 1) {
                or = ";";
            }
            dou = ",";
            sql = sql + " teacherId='" + id + "' and mark=0 and dmy='" + jsonArray.parseObject(jsonArray.get(0).toString()).getString("sj") + "' and  substring_index(substring_index(a.classHours,',',b.help_topic_id+1),',',-1) in(";
            String[] a = (StringUtils.strip(jsonArray.parseObject(jsonArray.get(i).toString()).getString("keshi"), "[]")).replace("\"", "").split(",");
            for (int v = 0; v < a.length; v++) {
                if (v == a.length - 1) {
                    dou = ")";
                }
                sql = sql + "'" + a[v] + "'";
                sql = sql + dou;
            }
            sql = sql + or;
        }


        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> ar = new ArrayList<String>();
        while (resultSet.next()) {
            String jie = resultSet.getString("b");
            ar.add(jie);
        }

        jdbc.guanbi(null, preparedStatement, connection);
        return ar;
    }

    /**
     * 查看用户当前月份的课
     *
     * @param userName
     * @return 返回当前有课的时间
     */

    @Override
    public JSONArray month(String userName, String m, String y) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String sql = "SELECT dmy,classHours FROM schooltreasure.curriculum   where  teacherId= (SELECT id FROM schooltreasure.teacher where userName='" + userName + "') and   month(dmy)='" + m + "'  and  year(dmy)='" + y + " '  and invalid=0  ;";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        JSONArray jsonArray = ResultSetToJson.resultSetToJsonArry(resultSet);

        jdbc.guanbi(resultSet, statement, connection);
        return jsonArray;
    }

    /**
     * 更改查重
     *
     * @param curriCulum
     * @return 返回1或0
     */
    @Override
    public ArrayList<String> Upcheck(CurriCulum curriCulum) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String[] ks = curriCulum.getClassHours().split(",");
        String dou = ",";
        String sql = "select a.id,a.mark,substring_index(substring_index(a.classHours,',',b.help_topic_id+1),',',-1)as b from schooltreasure.curriculum a join mysql.help_topic b on b.help_topic_id < (length(a.classHours) - length(replace(a.classHours,',',''))+1) where teacherId='" + curriCulum.getTeacherId() + "' and dmy='" + curriCulum.getDmy() + "' and  substring_index(substring_index(a.classHours,',',b.help_topic_id+1),',',-1) in(";
        for (int i = 0; i < ks.length; i++) {
            if (i == ks.length - 1) {
                dou = ")";
            }
            sql = sql + "'" + ks[i] + "'";
            sql = sql + dou;
        }


        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<String> ar = new ArrayList<String>();
        String jie = "";
        while (resultSet.next()) {
            jie = resultSet.getString("b");
            ar.add(jie);
        }

        jdbc.guanbi(null, preparedStatement, connection);
        return ar;
    }

    @Override
    public JSONArray selectAll(String classes, String teaName, String date_One, String date_Two, int all,int limitPag,int splitPag) throws SQLException {
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        String sql=" SELECT a.id,b.id,d.id,a.dmy,a.mark,b.teacherName,d.className FROM schooltreasure.curriculum as a  left join schooltreasure.teacher as b on a.teacherId=b.id left join  schooltreasure.time as c on a.classHours=c.id  left join schooltreasure.classtable as d on a.classID=d.classId where a.invalid=0";
        if(classes!=null && classes!="" && !classes.equals("-1")){
            sql=sql+" and d.id='"+classes+"'";

        }
        if(teaName!=null && teaName!=""  && !teaName.equals("-1")){
            sql=sql+" and b.id='"+teaName+"'";
        }
        if(date_One!=null && date_Two!=null && date_One!="" && date_Two!=""){
            sql=sql+" and a.dmy between '"+date_One+"' and '"+date_Two+"'";
        }
        if(all==1){
            sql=sql+" and a.mark='"+all+"'";
        }
        if(all==0){
            sql=sql+" and a.mark='"+all+"'";
        }
        if(all==-1){
            sql=sql+" and 1=1";
        }
        sql=sql+" limit "+limitPag+","+splitPag+"";

        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        ResultSet resultSet=preparedStatement.executeQuery(sql);
        JSONArray jsonArray=new JSONArray();
        while (resultSet.next()){
            JSONObject jsonObject=new JSONObject();
            jsonObject.put("aid",resultSet.getInt("id"));
            jsonObject.put("id",resultSet.getInt("id"));
            jsonObject.put("id",resultSet.getInt("id"));
            jsonObject.put("teacherName",resultSet.getString("teacherName"));
            jsonObject.put("className",resultSet.getString("className"));
            jsonObject.put("dmy",resultSet.getString("dmy"));
            jsonObject.put("mark",resultSet.getString("mark"));
            jsonArray.add(jsonObject);
        }

        jdbc.guanbi(null,preparedStatement,connection);
        return jsonArray;
    }






    /**
     * 班级查重
     *
     * @param jsonArray
     * @return
     */
    @Override
    public ArrayList<String> banji(JSONArray jsonArray) throws SQLException {
        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String dou = ",";
        String or = "or";
        String sql = "select DISTINCT c.className from schooltreasure.curriculum a join mysql.help_topic b on b.help_topic_id < (length(a.classHours) - length(replace(a.classHours,',',''))+1) left join  schooltreasure.classtable as c on c.classId=a.classId  where";
        for (int i = 0; i < jsonArray.size(); i++) {
            if (i == jsonArray.size() - 1) {
                or = ";";
            }
            dou = ",";
            sql = sql + " a.classID='" + jsonArray.parseObject(jsonArray.get(i).toString()).getString("ban") + "' and mark=0 and dmy='" + jsonArray.parseObject(jsonArray.get(0).toString()).getString("sj") + "' and  substring_index(substring_index(a.classHours,',',b.help_topic_id+1),',',-1) in(";
            String[] a = (StringUtils.strip(jsonArray.parseObject(jsonArray.get(i).toString()).getString("keshi"), "[]")).replace("\"", "").split(",");
            for (int v = 0; v < a.length; v++) {
                if (v == a.length - 1) {
                    dou = ")";
                }
                sql = sql + "'" + a[v] + "'";
                sql = sql + dou;
            }
            sql = sql + or;
        }


        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> ar = new ArrayList<String>();
        while (resultSet.next()) {
            String jie = resultSet.getString("className");
            ar.add(jie);
        }

        jdbc.guanbi(null, preparedStatement, connection);
        return ar;
    }

    /**
     * 获取总页数
     * @return
     */
    @Override
    public int zong(String classes, String teaName, String date_One, String date_Two,int all) throws SQLException {

        jdbc jdbc = new jdbc();
        Connection connection = jdbc.lianjie();
        String sql = " SELECT count(*)as ab FROM schooltreasure.curriculum as a  left join schooltreasure.teacher as b on a.teacherId=b.id left join  schooltreasure.time as c on a.classHours=c.id  left join schooltreasure.classtable as d on a.classID=d.classId where a.invalid=0";
        if (classes != null && classes != "" && !classes.equals("-1")) {
            sql = sql + " and d.id='" + classes + "'";

        }
        if (teaName != null && teaName != "" && !teaName.equals("-1")) {
            sql = sql + " and b.id='" + teaName + "'";
        }
        if (date_One != null && date_Two != null && date_One != "" && date_Two != "") {
            sql = sql + " and a.dmy between '" + date_One + "' and '" + date_Two + "'";
        }
        if (all == 1) {
            sql = sql + " and a.mark='" + all + "'";
        }
        if (all == 0) {
            sql = sql + " and a.mark='" + all + "'";
        }
        if (all == -1) {
            sql = sql + " and 1=1";
        }

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery(sql);
        int zon=0;
        while (resultSet.next()) {

            zon = resultSet.getInt("ab");
        }


        jdbc.guanbi(null, preparedStatement, connection);
        return zon;

    }
}